1. Field of the Invention
The invention relates to computer systems, and more particularly to a method and mechanism for calling an external procedure in a database system in which the external procedure contains embedded database query language statements.
2. Background
Modern database systems are normally configured to allow users to interface with the database system using database query language statements such as Structured Query Language (xe2x80x9cSQLxe2x80x9d) statements. SQL is a language specifically designed to access and interface with data/data structures in a relational database system. To provide increased flexibility for querying a database, some database manufacturers provide extensions to the SQL language standard. For example, xe2x80x9cPL/SQLxe2x80x9d is a SQL-derived database query language developed by Oracle Corporation that provides procedural language extensions to the generally non-procedural SQL language standard.
While SQL and SQL-derived languages are well suited for database access purposes, these specialized languages lack the flexibility and power of many general purpose programming languages such as xe2x80x9cCxe2x80x9d and xe2x80x9cC++xe2x80x9d. This is particularly true for programming operations that need to be performed outside the context of the database (e.g., operating system calls). To enhance the capabilities of the database, some database systems provide a mechanism that allow users to invoke general purpose programming language functions or routines that are external to the database (sometimes referred to as xe2x80x9cexternal routinesxe2x80x9d or xe2x80x9cexternal proceduresxe2x80x9d). One such mechanism for implementing external procedures is described in co-pending U.S. patent application Ser. No. 08/880,326, entitled xe2x80x9cApparatus and Method for Calling External Routines in a Database Systemxe2x80x9d, filed on Jun. 23, 1997, which is hereby incorporated by reference in its entirety. In this approach, an external procedure is written in a general purpose programming language, such as C, that is compiled and archived into a shared library. The compiled external procedure is thereafter dynamically loaded at runtime or whenever it is invoked at the database server.
Although a general purpose programming language provides greater flexibility and power when compared to a SQL or SQL-derived language, the SQL or SQL-derived language program is often much easier to develop and use to make calls to a database. This is due to the specialized nature of the SQL and SQL-derived language, which were specifically created to access and query data stored in database systems.
To illustrate this relative advantage of the SQL or SQL-derived language program, consider the program steps needed in a typical general purpose programming language program to perform the common database act of searching and retrieving from a database system. To correctly inter-operate with a database system, the general purpose programming language routine normally includes program statements from an applications programming interface (xe2x80x9cAPIxe2x80x9d) that was developed to access the database system. For example, the Oracle Call Interface (OCI) library is a set of APIs for the C and C++ language for accessing database systems available from Oracle Corporation. Similar API libraries are employed to access systems from other database vendors. The general programming language program normally requires numerous API calls and program statements to perform the common database act of querying and retrieving data, including, for example, statements and calls to define variables, access database data structures, perform data search, perform data comparison, and error handling statements. In contrast, the SQL or SQL-derived language program is able to utilize a single xe2x80x9cSELECTxe2x80x9d statement to perform the equivalent act of searching and retrieving data from a database.
Typically, general purpose programming languages such as C and C++ do not directly support statements written in a database query language such as SQL. However, hybrid languages have been developed incorporating elements of both general programming languages and database query languages. These hybrid languages allow programmers to specify database operations by embedding database query language statements (e.g., SQL) into the source code of general purpose program language programs. In many cases, a precompiler reads the source code files that contain embedded SQL statements and generates source code files in which the embedded SQL statements have been replaced with calls to database access routines to perform the operation specified in the embedded SQL statements that they replace. Examples of hybrid language types include Pro*C/C++ and Pro* COBOL.
With the arrival of such hybrid languages, it is now desirable to provide a mechanism that allows procedures and routines in such hybrid languages to be called even if these procedures and routines are external to the database. It is particularly desirable to allow embedded database query language statements in these external procedures to execute within the same database connection and transaction as the entity that called the external procedure, to allow the embedded database query language statements in the external procedure to call back into the database.
The present invention is directed to a method and mechanism for calling an external procedure in a database system in which the external procedure contains embedded database query language statements. According to an aspect of the invention, the external procedure executes using the same database connection and transaction as the original calling entity for that external procedure. In an embodiment, this is performed by identifying connection information for the entity that called the external procedure, mapping that connection information to equivalent connection data structures maintained for the external procedure, and thereafter executing the external procedure within the same session previously opened by the entity.
Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims.